Extracting Text Using TEXTBEFORE and TEXTAFTER in Excel

Course: Advanced Excel

Topic: Extracting Text Using TEXTBEFORE and TEXTAFTER

Course Link: Advanced Excel Course

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to TEXTBEFORE and TEXTAFTER

Microsoft Excel provides powerful text functions that help users extract, clean, and organize text data. The TEXTBEFORE and TEXTAFTER functions are modern Excel functions designed to extract text from a cell based on a specified delimiter or character.

A delimiter is a character or text string that separates different parts of a value. Common delimiters include spaces, commas, hyphens, underscores, colons, slashes, and the @ symbol.

For example, if a cell contains [email protected], TEXTBEFORE can extract John.Smith before the @ symbol, while TEXTAFTER can extract example.com after the @ symbol.

These functions are especially useful when cleaning customer records, separating names, extracting domain names, processing product codes, and preparing data for reports.

2. Learning Objectives

• Understand the purpose of TEXTBEFORE and TEXTAFTER.
• Learn the syntax and arguments of both functions.
• Extract text before or after a specified delimiter.
• Handle multiple delimiters and repeated characters.
• Understand error handling and optional arguments.
• Combine text extraction with other Excel functions.
• Apply these functions to practical business datasets.

3. Understanding the TEXTBEFORE Function

The TEXTBEFORE function returns the text that appears before a specified delimiter within a text string. It is useful when you need to isolate the first or a particular portion of a value.

Syntax

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Function Arguments

Example 1: Extracting a First Name

Suppose cell A2 contains the full name Rahul Sharma. To extract the first name, use:

=TEXTBEFORE(A2," ")

Result: Rahul

Explanation: Excel searches for the first space and returns all characters before it.

Example 2: Extracting a Username from an Email Address

Suppose A2 contains [email protected]. Use the following formula:

=TEXTBEFORE(A2,"@")

Result: rahul.sharma

Explanation: The formula extracts all text before the @ symbol.

Example 3: Extracting a Product Category

Suppose A2 contains Electronics-Mobile-001. To extract the text before the first hyphen, use:

=TEXTBEFORE(A2,"-")

Result: Electronics

Example 4: Extracting Text Before the Second Delimiter

Suppose A2 contains India-Maharashtra-Mumbai. To extract everything before the second hyphen, use:

=TEXTBEFORE(A2,"-",2)

Result: India-Maharashtra

Explanation: The third argument specifies the second occurrence of the delimiter.

4. Understanding the TEXTAFTER Function

The TEXTAFTER function returns the text that appears after a specified delimiter. It is useful for extracting the last name, email domain, file extension, location, or another text component.

Syntax

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Function Arguments

Example 1: Extracting a Last Name

Suppose A2 contains Rahul Sharma. Use:

=TEXTAFTER(A2," ")

Result: Sharma

Explanation: Excel returns the text following the first space.

Example 2: Extracting an Email Domain

Suppose A2 contains [email protected]. Use:

=TEXTAFTER(A2,"@")

Result: gmail.com

Example 3: Extracting a File Extension

Suppose A2 contains Quarterly_Report.xlsx. Use:

=TEXTAFTER(A2,".")

Result: xlsx

This example works when the filename contains a single period. For filenames with multiple periods, use a negative instance number to extract the text after the last period.

=TEXTAFTER(A2,".",-1)

Example result: For Quarterly.Report.Final.xlsx, the result is xlsx.

Example 4: Extracting a City from a Location String

Suppose A2 contains India-Maharashtra-Mumbai. To extract everything after the second hyphen, use:

=TEXTAFTER(A2,"-",2)

Result: Mumbai

5. Difference Between TEXTBEFORE and TEXTAFTER

6. Practical Dataset Example

Consider the following employee dataset. The Employee Details column contains a full name followed by an employee ID, separated by a hyphen.

To extract the employee name in cell B2, enter:

=TEXTBEFORE(A2,"-")

To extract the employee ID in cell C2, enter:

=TEXTAFTER(A2,"-")

Copy both formulas down the columns to process the remaining records. This approach reduces manual work and helps maintain consistent formatting.

7. Using Instance Numbers with Multiple Delimiters

Sometimes a text string contains the same delimiter multiple times. The optional instance_num argument allows you to choose which occurrence should determine the extraction boundary.

Example Dataset

Negative instance numbers count delimiter occurrences from the end of the string. For example:

=TEXTBEFORE(A2,"-",-1)

For India-Maharashtra-Mumbai, this returns India-Maharashtra because the final hyphen is used as the boundary.

=TEXTAFTER(A2,"-",-1)

This returns Mumbai because extraction begins after the final hyphen.

8. Handling Missing Delimiters and Errors

If the specified delimiter does not occur in the text, TEXTBEFORE and TEXTAFTER normally return a #N/A error. Use the optional if_not_found argument to provide a clearer result.

Example 1: Custom Result When a Delimiter Is Missing

=TEXTBEFORE(A2,"-","",0,0,"Delimiter Not Found")

This returns Delimiter Not Found if the hyphen cannot be found. The other optional arguments are explicitly set to their default-style values in this example.

Example 2: Handling a Missing Email Separator

=TEXTAFTER(A2,"@",1,0,0,"Invalid Email")

If the @ symbol is missing, the formula returns Invalid Email instead of a #N/A error. This helps make data-quality issues easier to identify.

Example 3: Using IFERROR

=IFERROR(TEXTBEFORE(A2,"-"),"Check Data")

Explanation: IFERROR returns Check Data if the TEXTBEFORE calculation produces an error. This approach is useful when you want to handle different formula errors with one fallback message.

9. Case-Sensitive and Case-Insensitive Matching

The match_mode argument controls whether the delimiter comparison is case-sensitive.

• 0: Case-sensitive matching, which is the default.
• 1: Case-insensitive matching.

For example, suppose A2 contains ExcelTRAINING. The following formula searches for the lowercase delimiter training without distinguishing uppercase and lowercase letters:

=TEXTBEFORE(A2,"training",1,1)

Result: Excel

With case-sensitive matching, the lowercase delimiter would not match the uppercase letters in TRAINING.

10. Combining TEXTBEFORE and TEXTAFTER with Other Functions

Combining text extraction functions with other Excel functions makes data-cleaning workflows more flexible.

Example 1: Removing Extra Spaces

=TRIM(TEXTBEFORE(A2,"-"))

This extracts the text before the hyphen and removes unnecessary leading, trailing, and repeated ordinary spaces.

Example 2: Converting Extracted Text to Uppercase

=UPPER(TEXTAFTER(A2,"-"))

This extracts the text after the hyphen and converts it to uppercase.

Example 3: Extracting a Website Domain

Suppose A2 contains https://www.example.com/products. To extract the domain portion after the protocol and before the first slash, use:

=TEXTBEFORE(TEXTAFTER(A2,"://"),"/")

Result: www.example.com

This formula assumes the URL contains the protocol separator :// and a slash after the domain.

Example 4: Extracting a Department from an Employee Code

Suppose A2 contains EMP-HR-2026-015. To extract the department code, use:

=TEXTAFTER(TEXTBEFORE(A2,"-",2),"-")

Result: HR

The inner TEXTBEFORE extracts EMP-HR, and the outer TEXTAFTER returns the text after the first hyphen in that result.

11. Common Errors and Troubleshooting

12. Best Practices for Using TEXTBEFORE and TEXTAFTER

• Identify the correct delimiter before writing the formula.
• Check whether the delimiter occurs more than once in the text.
• Use instance_num carefully when extracting text from structured codes.
• Use the if_not_found argument to make formulas more readable and user-friendly.
• Combine the functions with TRIM when extra spaces may affect the output.
• Test formulas with blank cells, missing delimiters, and inconsistent source data.
• Use cell references instead of hard-coded text whenever possible.
• Confirm that your Excel version supports TEXTBEFORE and TEXTAFTER before using them in shared workbooks.
• Use clear column headings for extracted values to improve reporting and data analysis.

13. Practice Exercises

Complete the following exercises to strengthen your understanding of TEXTBEFORE and TEXTAFTER.

Exercise 1: Extract First and Last Names

Cell A2 contains Ananya Deshmukh.

• Extract the first name using TEXTBEFORE.
• Extract the last name using TEXTAFTER.

Exercise 2: Extract Email Components

Cell A2 contains [email protected].

• Extract the username before @.
• Extract the domain after @.

Exercise 3: Extract a Product Code

Cell A2 contains FURNITURE-CHAIR-250.

• Extract the first category using TEXTBEFORE.
• Extract the final product number using TEXTAFTER with a negative instance number.

Exercise 4: Handle Missing Delimiters

Cell A2 contains AdvancedExcel.

• Write a TEXTBEFORE formula that returns Delimiter Missing if a hyphen is not present.
• Write a TEXTAFTER formula that returns Delimiter Missing if a hyphen is not present.

Exercise 5: Extract a File Extension

Cell A2 contains monthly.report.final.xlsx. Write a formula that returns only the final file extension.

14. Mini Project: Cleaning Customer and Product Data

Project Objective: Use TEXTBEFORE and TEXTAFTER to transform unstructured text into organized Excel columns.

Sample Data:

Project Steps:

1. Enter the raw data into column A.
2. Identify the delimiter separating the required components.
3. Use TEXTBEFORE to extract values that appear before a delimiter.
4. Use TEXTAFTER to extract values that appear after a delimiter.
5. Use instance_num for strings containing repeated delimiters.
6. Add error handling for records that do not follow the expected format.
7. Compare the original data with the extracted columns and correct inconsistencies.

Expected Outcome: A clean, structured dataset that can be used for sorting, filtering, PivotTables, formulas, and reporting.

15. Interview Questions and Answers

Q1. What is the TEXTBEFORE function in Excel?

Answer: TEXTBEFORE returns the portion of a text string that appears before a specified delimiter.

Q2. What is the TEXTAFTER function?

Answer: TEXTAFTER returns the portion of a text string that appears after a specified delimiter.

Q3. What is a delimiter?

Answer: A delimiter is a character or text string used to separate portions of text, such as a space, comma, hyphen, or @ symbol.

Q4. How can you extract an email domain?

Answer: Use =TEXTAFTER(A2,"@") when A2 contains an email address.

Q5. How do you extract text before the second hyphen?

Answer: Use =TEXTBEFORE(A2,"-",2).

Q6. How do you extract text after the last hyphen?

Answer: Use =TEXTAFTER(A2,"-",-1).

Q7. What happens when a delimiter is not found?

Answer: The function normally returns a #N/A error. You can provide a custom result using the if_not_found argument.

Q8. What is the purpose of match_mode?

Answer: It determines whether delimiter matching is case-sensitive. Use 0 for case-sensitive matching and 1 for case-insensitive matching.

Q9. Can TEXTBEFORE and TEXTAFTER be combined with other functions?

Answer: Yes. They can be combined with functions such as TRIM, UPPER, LOWER, IFERROR, and other text functions.

Q10. Are TEXTBEFORE and TEXTAFTER available in every Excel version?

Answer: No. They are modern text functions available in supported Excel versions, including Microsoft 365 and newer perpetual versions that include these functions. Older versions may require alternatives such as LEFT, RIGHT, MID, FIND, or SEARCH.

16. Quick Revision Checklist

• TEXTBEFORE extracts text before a delimiter.
• TEXTAFTER extracts text after a delimiter.
• The delimiter can be a character or a text string.
• instance_num selects a specific delimiter occurrence.
• Negative instance numbers count occurrences from the end.
• match_mode controls case-sensitive matching.
• if_not_found provides a custom result when a delimiter is missing.
• TRIM and IFERROR can help create cleaner and more reliable formulas.
• Always test formulas with realistic data and unexpected input.

17. Conclusion

TEXTBEFORE and TEXTAFTER are valuable functions for extracting specific portions of text in Microsoft Excel. They simplify tasks such as separating names, processing email addresses, extracting product codes, cleaning filenames, and organizing business records.

By understanding delimiters, instance numbers, matching options, and error handling, you can build efficient formulas and reduce manual data-cleaning work. These functions are particularly useful in advanced Excel reporting, data preparation, and analysis workflows.

To explore more advanced Excel concepts, visit the JustAcademy Advanced Excel Course, or register for a course demo.